package com.info.zhiduoduo.repository.mapper.foreign;

import com.baomidou.mybatisplus.mapper.BaseMapper;
import com.info.zhiduoduo.repository.dto.foreign.ExportForeignProjectDto;
import com.info.zhiduoduo.repository.dto.foreign.ForeignProjectDto;
import com.info.zhiduoduo.repository.entity.foreign.ForeignProject;
import org.apache.ibatis.annotations.Select;

import java.util.Date;
import java.util.List;

/**
 * <p>
 * 记录第三方推送的项目 Mapper 接口
 * </p>
 *
 * @author cuichenglong
 * @since 2020-08-31
 */
public interface ForeignProjectMapper extends BaseMapper<ForeignProject> {

    @Select(" SELECT * FROM zdd_foreign_project WHERE person_no = #{personNo} ORDER BY createtime DESC LIMIT 1 ")
    ForeignProject getNewestProject(String personNo);


    @Select(" SELECT fp.project_id,fp.project_no,fp.project_name,fp.compid,fp.amount,fp.pay_state,fp.createtime,fp.project_desc, " +
            " ftr.partner_order_no,ftr.errormsg,ftr.finishtime,ca.realname, ca.crowdsourceid, c.compname, " +
            " fi.state as invoiceState " +
            " FROM zdd_foreign_project fp " +
            " LEFT JOIN zdd_foreign_trade_record ftr ON fp.project_id = ftr.project_id " +
            " LEFT JOIN zdd_foreign_invoice fi ON fi.project_id = fp.project_id " +
            " LEFT JOIN zdd_crowdsource_account ca on ca.crowdsourceid = fp.crowdsourceid " +
            " LEFT JOIN zdd_company c on c.compid = fp.compid " +
            " where concat(fp.project_name,fp.project_no,ca.realname,ftr.partner_order_no) like '%${name}%' " +
            " AND fp.state = 1 " +
            " AND fp.createtime between #{startDate} and #{endDate} " +
            " ORDER BY fp.createtime DESC ")
    List<ForeignProjectDto> projectList(String name, Date startDate, Date endDate);



    @Select(" SELECT fp.createtime,fp.project_name,ca.realname,fp.amount,cir.month, " +
            " case when fp.invoice_state = 1  then '未开票' when fp.invoice_state = 2  then '开票中' " +
            " when fp.invoice_state = 3  then '开票成功' when fp.invoice_state = 4  then '开票失败' end as invoiceState, " +
            " case when fp.pay_state = 1  then '未付款' when fp.pay_state = 2  then '付款中' " +
            " when fp.pay_state = 3  then '付款成功' when fp.pay_state = 4  then '付款失败' end as payState, " +
            " fp.project_no, c.compname AS companyName, " +
            " ftr.partner_order_no,cir.gq_project_number,ftr.finishtime,cir.msg " +
            " FROM zdd_foreign_project fp " +
            " LEFT JOIN zdd_foreign_trade_record ftr ON fp.project_id = ftr.project_id " +
            " LEFT JOIN zdd_crowdsource_invoice_record cir ON cir.id = fp.invoice_id " +
            " LEFT JOIN zdd_crowdsource_account ca on ca.crowdsourceid = fp.crowdsourceid " +
            " LEFT JOIN zdd_company c on c.compid = fp.compid " +
            " where concat(fp.project_name,fp.project_no,ca.realname,ftr.partner_order_no) like '%${name}%' " +
            " AND fp.state = 1 " +
            " AND fp.createtime between #{startDate} and #{endDate} " +
            " ORDER BY fp.createtime DESC ")
    List<ExportForeignProjectDto> exportForeignProjectList(String name, Date startDate, Date endDate);

    @Select(" SELECT * from zdd_foreign_project WHERE pay_state = 1 ")
    List<ForeignProject> listPendingPayProject();

    @Select(" SELECT * from zdd_foreign_project WHERE pay_state = 3 and invoice_id = 0 ")
    List<ForeignProject> brushForeignInvoice();

}
